﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class YX_CashChequeBLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT r,
       N_CASHCHEQUE_ID,
       CMY_NAME,
       C_NAME,
       C_CASHCHEQUE_DRAWERACCOUNT,
       C_CASHCHEQUE_PAYEENAME,
       N_CASHCHEQUE_INVOICEVALUE,
       to_char(D_CASHCHEQUE_STARTDATE, 'yyyy-mm-dd') D_CASHCHEQUE_STARTDATE,
       C_CASHCHEQUE_CODE,
       C_CASHCHEQUE_USE,
       C_CASHCHEQUE_REMARK
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + orderby + @") as r,
       YXCC.N_CASHCHEQUE_ID AS N_CASHCHEQUE_ID,
       SCOM.CMY_NAME AS CMY_NAME,
       SBAN.C_NAME AS C_NAME,
       YXCC.C_CASHCHEQUE_DRAWERACCOUNT AS C_CASHCHEQUE_DRAWERACCOUNT,
       YXCC.C_CASHCHEQUE_PAYEENAME AS C_CASHCHEQUE_PAYEENAME,
       YXCC.N_CASHCHEQUE_INVOICEVALUE AS N_CASHCHEQUE_INVOICEVALUE,
       YXCC.D_CASHCHEQUE_STARTDATE AS D_CASHCHEQUE_STARTDATE,
       YXCC.C_CASHCHEQUE_CODE AS C_CASHCHEQUE_CODE,
       YXCC.C_CASHCHEQUE_USE AS C_CASHCHEQUE_USE,
       YXCC.C_CASHCHEQUE_REMARK AS C_CASHCHEQUE_REMARK
  FROM YX_CASHCHEQUE YXCC
  LEFT JOIN YX_ACCOUNT YXCT
    ON YXCC.C_CASHCHEQUE_DRAWERACCOUNT = YXCT.ACCOUNTNO
  LEFT JOIN SYS_COMPANY SCOM
    ON YXCC.N_CASHCHEQUE_DRAWERCMPID = SCOM.CMY_ID
  LEFT JOIN SYS_BANK SBAN
    ON YXCC.N_CASHCHEQUE_DRAWERBANKID = SBAN.N_ID
 WHERE /*YXCT.N_BANK_ID = YXCC.N_CASHCHEQUE_DRAWERBANKID
   AND*/ YXCT.ACCOUNTTYPE=1");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr)
        {
            //           string sql = string.Format(@"SELECT COUNT(1)
            // FROM YX_CASHCHEQUE YXCC
            // LEFT JOIN YX_ACCOUNT YXCT
            //   ON YXCC.C_CASHCHEQUE_DRAWERACCOUNT = YXCT.ACCOUNTNO
            // LEFT JOIN SYS_COMPANY SCOM
            //   ON YXCC.N_CASHCHEQUE_DRAWERCMPID = SCOM.CMY_ID
            // LEFT JOIN SYS_BANK SBAN
            //   ON YXCC.N_CASHCHEQUE_DRAWERBANKID = SBAN.N_ID
            //WHERE /*YXCT.COMPANY_ID = YXCC.N_CASHCHEQUE_DRAWERCMPID
            //  AND */YXCT.N_BANK_ID = YXCC.N_CASHCHEQUE_DRAWERBANKID
            //  AND YXCT.ACCOUNTTYPE=1 " + whereStr);

            string sql = string.Format(@"SELECT COUNT(*)
  FROM (SELECT
       YXCC.N_CASHCHEQUE_ID AS N_CASHCHEQUE_ID,
       SCOM.CMY_NAME AS CMY_NAME,
       SBAN.C_NAME AS C_NAME,
       YXCC.C_CASHCHEQUE_DRAWERACCOUNT AS C_CASHCHEQUE_DRAWERACCOUNT,
       YXCC.C_CASHCHEQUE_PAYEENAME AS C_CASHCHEQUE_PAYEENAME,
       YXCC.N_CASHCHEQUE_INVOICEVALUE AS N_CASHCHEQUE_INVOICEVALUE,
       YXCC.D_CASHCHEQUE_STARTDATE AS D_CASHCHEQUE_STARTDATE,
       YXCC.C_CASHCHEQUE_CODE AS C_CASHCHEQUE_CODE,
       YXCC.C_CASHCHEQUE_USE AS C_CASHCHEQUE_USE,
       YXCC.C_CASHCHEQUE_REMARK AS C_CASHCHEQUE_REMARK
  FROM YX_CASHCHEQUE YXCC
  LEFT JOIN YX_ACCOUNT YXCT
    ON YXCC.C_CASHCHEQUE_DRAWERACCOUNT = YXCT.ACCOUNTNO
  LEFT JOIN SYS_COMPANY SCOM
    ON YXCC.N_CASHCHEQUE_DRAWERCMPID = SCOM.CMY_ID
  LEFT JOIN SYS_BANK SBAN
    ON YXCC.N_CASHCHEQUE_DRAWERBANKID = SBAN.N_ID
 WHERE /*YXCT.N_BANK_ID = YXCC.N_CASHCHEQUE_DRAWERBANKID
   AND*/ YXCT.ACCOUNTTYPE=1");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            sql += @" ) TT";

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 根据编号查询银行汇票明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public YX_CashCheque SelectById(string id)
        {
            string sql = string.Format(@"select * from YX_CashCheque a where a.N_CASHCHEQUE_ID= " + id);

            DataTable dt = Query(sql);

            YX_CashCheque model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new YX_CashCheque();
                    model.N_CASHCHEQUE_ID = Convert.ToDecimal(id);
                    model.N_CASHCHEQUE_DRAWERCMPID = decimal.Parse(dr["N_CASHCHEQUE_DRAWERCMPID"].ToString());
                    model.N_CASHCHEQUE_DRAWERBANKID = decimal.Parse(dr["N_CASHCHEQUE_DRAWERBANKID"].ToString());
                    model.C_CASHCHEQUE_DRAWERACCOUNT = dr["C_CASHCHEQUE_DRAWERACCOUNT"].ToString();
                    model.C_CASHCHEQUE_PAYEENAME = dr["C_CASHCHEQUE_PAYEENAME"].ToString();
                    model.D_CASHCHEQUE_STARTDATE = Convert.ToDateTime(dr["D_CASHCHEQUE_STARTDATE"].ToString());
                    model.C_CASHCHEQUE_USE = dr["C_CASHCHEQUE_USE"].ToString().ToString();
                    model.N_CASHCHEQUE_INVOICEVALUE = Convert.ToDecimal(dr["N_CASHCHEQUE_INVOICEVALUE"].ToString());
                    model.C_CASHCHEQUE_CODE = dr["C_CASHCHEQUE_CODE"].ToString();
                    model.C_CASHCHEQUE_REMARK = dr["C_CASHCHEQUE_REMARK"].ToString();
                }
            }

            return model;
        }

        public int INSERT(YX_CashCheque model, string operName)
        {
            model.N_CASHCHEQUE_ID = GetSequence("SEQ_N_CASHCHEQUE_ID");
            string sql = string.Format(@"insert into yx_cashcheque
  (n_cashcheque_id,
   n_cashcheque_drawercmpid,
   n_cashcheque_drawerbankid,
   c_cashcheque_draweraccount,
   c_cashcheque_payeename,
   n_cashcheque_invoicevalue,
   d_cashcheque_startdate,
   c_cashcheque_code,
   c_cashcheque_use,
   c_cashcheque_remark)
values
  ({0},
   {1},
   {2},
   '{3}',
   '{4}',
   '{5}',
   TO_DATE('{6}', 'YYYY-MM-DD HH24:MI:SS'),
   '{7}',
   '{8}',
   '{9}')", model.N_CASHCHEQUE_ID, model.N_CASHCHEQUE_DRAWERCMPID, model.N_CASHCHEQUE_DRAWERBANKID, model.C_CASHCHEQUE_DRAWERACCOUNT, model.C_CASHCHEQUE_PAYEENAME, model.N_CASHCHEQUE_INVOICEVALUE, model.D_CASHCHEQUE_STARTDATE, model.C_CASHCHEQUE_CODE, model.C_CASHCHEQUE_USE, model.C_CASHCHEQUE_REMARK);

            int count = NoQuery(sql, "新增银行汇票", "INSERT", operName);
            return count;
        }

        public int UPDATE(YX_CashCheque model, string operName)
        {
            string sql = string.Format(@"update yx_cashcheque
   set n_cashcheque_drawercmpid   = {0},
       n_cashcheque_drawerbankid  = {1},
       c_cashcheque_draweraccount = '{2}',
       c_cashcheque_payeename     = '{3}',
       n_cashcheque_invoicevalue  = {4},
       d_cashcheque_startdate     = TO_DATE('{5}', 'YYYY-MM-DD HH24:MI:SS'),
       c_cashcheque_code          = '{6}',
       c_cashcheque_use           = '{7}',
       c_cashcheque_remark        = '{8}'
 where n_cashcheque_id = {9}", model.N_CASHCHEQUE_DRAWERCMPID, model.N_CASHCHEQUE_DRAWERBANKID, model.C_CASHCHEQUE_DRAWERACCOUNT, model.C_CASHCHEQUE_PAYEENAME, model.N_CASHCHEQUE_INVOICEVALUE, model.D_CASHCHEQUE_STARTDATE, model.C_CASHCHEQUE_CODE, model.C_CASHCHEQUE_USE, model.C_CASHCHEQUE_REMARK, model.N_CASHCHEQUE_ID);
            int count = NoQuery(sql, operName);
            return count;
        }
    }
}